-- MedAssist Clinic Management System
-- Transaction scenarios
USE `medassets`;

-- COMMIT scenario
START TRANSACTION;

INSERT INTO Patient
(FirstName, LastName, Gender, DOB, Phone, Email, Address, MedicalHistory)
VALUES
('Transaction', 'CommitTest', 'Female', '1990-01-01',
'07000000000', 'commit.test@example.com', 'London, UK', 'Test record');

COMMIT;

SELECT * FROM Patient
WHERE Email = 'commit.test@example.com';

-- ROLLBACK scenario
START TRANSACTION;

INSERT INTO Patient
(FirstName, LastName, Gender, DOB, Phone, Email, Address, MedicalHistory)
VALUES
('Transaction', 'RollbackTest', 'Male', '1990-01-01',
'07000000001', 'rollback.test@example.com', 'London, UK', 'Test record');

ROLLBACK;

SELECT * FROM Patient
WHERE Email = 'rollback.test@example.com';
